﻿using BwTMusicModels;
using HelperModels;
using HwjUIDI.Utility.DatabaseHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection.Emit;
using System.Text;
using System.Threading.Tasks;

namespace BwTServices.Music
{
    public class MusicServices
    {
        /// <summary>
        /// 获取所有的音乐信息
        /// </summary>
        /// <param name="filter"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<MusicHelper> GetAllNorthMusic(MusicFilter filter, out int total)
        {
            List<MusicHelper> Lists = new List<MusicHelper>();
            total = 0;
            try
            {
                if (filter != null)
                {
                    if (filter.PageSize > 0 && filter.CurrentPage > 0)
                    {
                        StringBuilder sbSQL = new StringBuilder();
                        List<SqlParameter> listParam = new List<SqlParameter>();
                        sbSQL.Append("select top ");
                        sbSQL.Append(filter.PageSize);
                        sbSQL.Append(" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.MusicId) as rownumber,A.MusicId ,A.MusicName,B.SingerName,A.MusicSinger,A.MusicImgUrl,C.ThemeName,D.GenreName,A.MusicClickCount,A.MusicChooseCount,A.MusicBitRate,A.MusicLong,A.MusicUrl ,E.AlbumName from North_Music A left join North_Singer B on A.MusicSinger=B.SingerId LEFT JOIN North_Theme C ON A.MusicCategory=C.ThemeId left join North_Genre D on A.MusicTag=D.GenreId left join North_Album E on A.MusicAlbum=E.AlbumId where A.IsVaild= 'Y' ");
                        if (!string.IsNullOrEmpty(filter.name))
                        {
                            sbSQL.Append(" and A.MusicName like '%' + @Name + '%'");
                            SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar);
                            param.Value = filter.name;
                            listParam.Add(param);
                        }
                        if (!string.IsNullOrEmpty(filter.singer))
                        {
                            sbSQL.Append(" and B.SingerName like '%' + @Singername + '%'");
                            SqlParameter param = new SqlParameter("@Singername", SqlDbType.NVarChar);
                            param.Value = filter.singer;
                            listParam.Add(param);
                        }
                        sbSQL.Append(") WTX where rownumber > (");
                        sbSQL.Append((filter.CurrentPage - 1) * filter.PageSize);
                        sbSQL.Append(")");
                        using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                        {
                            while (dr.Read())
                            {
                                int iIndex = 0;
                                MusicHelper list = new MusicHelper();
                                list.Musics = new ModelsHelper().GetNorthMusicModels(dr);
                                list.Singer = new ModelsHelper().GetNorthSingerModels(dr);
                                list.Genres = new ModelsHelper().GetNorthGenreModels(dr);
                                list.Themes = new ModelsHelper().GetNorthThemeModels(dr);
                                list.Albums = new ModelsHelper().GetNorthAlbumModels(dr);
                                Lists.Add(list);
                                if (iIndex == 0)
                                {
                                    total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                                }
                                iIndex++;
                            }
                        }

                    }
                   
                }
             
            }
            catch (Exception ex) 
            {
               
            }         
            return Lists;
        }
        /// <summary>
        /// 获取订单音乐
        /// </summary>
        /// <param name="lists"></param>
        /// <returns></returns>
        public List<MusicHelper> GetOrderMusic(string lists)
        {
            List<MusicHelper> bResult = new List<MusicHelper>();
            try
            {
                if (!string.IsNullOrEmpty(lists))
                {
                    string[] music = lists.Split(',');
                    for (int i = 0; i < music.Length; i++)
                    {
                        string sql = $"select * from North_Music a left join North_Singer b on a.MusicSinger=b.SingerId  where MusicId='{music[i]}'";
                        using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                        {
                            while (dr.Read())
                            {
                                MusicHelper list = new MusicHelper();
                                list.Musics = new ModelsHelper().GetNorthMusicModels(dr);
                                list.Singer = new ModelsHelper().GetNorthSingerModels(dr);                             
                                bResult.Add(list);
                            }
                        }
                    }
                }
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        public int IsNoVaildForMusic(North_Music music)
        {
            int bResult = 0;
            try
            {
                string sql = $"update North_Music set IsVaild='N' where MusicId='{music.MusicId}'";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {

            }
            return bResult;
        }
        public MusicHelper GetMusicHelperInfo(int id)
        {
            MusicHelper music = new MusicHelper();
            try
            {
                string sql = $"select  A.MusicId ,A.MusicName,B.SingerName,A.MusicSinger,A.MusicImgUrl,A.MusicBitRate,A.MusicLong ,E.AlbumName from  North_Music A left join North_Singer B on A.MusicSinger=B.SingerId left join North_Album E on A.MusicAlbum=E.AlbumId where A.IsVaild= 'Y' and A.MusicId={id}";
                using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                {
                    while (dr.Read())
                    {
                        music.Musics = new ModelsHelper().GetNorthMusicModels(dr);
                        music.Singer = new ModelsHelper().GetNorthSingerModels(dr);
                        music.Albums = new ModelsHelper().GetNorthAlbumModels(dr);                     
                    }
                }
            }
            catch (Exception)
            {

               
            }
            return music;
        }
     
    }
}
